﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Web;
using System.Text.RegularExpressions;


namespace CCPSIP
{
    public class OracleAddressBookKit
    {
        /// <summary>取得Oracle連線字串(SIP_CONTACT)</summary>
        private static string GetConnStrSipContact()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=SIP_CONTACT;pwd=kizuki33";
        }

        /// <summary>取得Oracle連線字串(ISENDER_ORACLE)</summary>
        private static string GetConnStrIsenderOracle()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=ISENDER;pwd=aKhJJFA2sWfEsQz0Zm5iwQi9gjiVND";
        }

        ///<summary>取得公司別(據點)和部門別列表</summary>
        public static List<Location> GetCompanyAndDepartmentList(bool CanSeeA1, bool CanSeePersonDetailInfo)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            List<Location> LocationList = new List<Location>();

            List<CompanyFirmMapping> CompanyFirmMappingList = GetCompanyFirmMappingList();//from oracle once, each location from memory(faster)
            //MappingList = InitializeCompanyFirmMapping(MappingList);  //from oracle each location

            List<CompanyAreacodeMapping> CompanyAreaMappingList = GetCompanyAreaMappingList();

            List<Person> PersonAllList = GetPersonAll(CanSeeA1, CanSeePersonDetailInfo);

            List<Person> AbroadPeopleList = GetAbroadPeople();

            List<DesktopPhoneCompare> DesktopPhoneList = GetDesktopPhoneList();

            //【01】取得所有據點
            foreach (CompanyFirmMapping m in CompanyFirmMappingList)
            {
                Location L = new Location();
                L.Name = m.LocationName;
                L.Code = m.CompanyFirmCode;
                L.Departments = GetDepartmentsByDeptNo(m, PersonAllList, AbroadPeopleList, DesktopPhoneList, CompanyAreaMappingList, CanSeePersonDetailInfo);

                LocationList.Add(L);
            }

            return LocationList;
        }

        /// <summary>取得部門列表(從Oracle)</summary>
        public static List<Department> GetDepartmentsByDeptNo(CompanyFirmMapping m, List<Person> PersonAllList, List<Person> AbroadPeopleList, List<DesktopPhoneCompare> DesktopPhoneList, List<CompanyAreacodeMapping> CompanyAreaMappingList, bool CanSeePersonDetailInfo)
        {
            //取得正常部門
            List<string> company_and_firm_code = m.CompanyFirmCode;
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_header = "SELECT DISTINCT substr(DEPT_NO,3,2) FROM ("
                                + " SELECT DISTINCT DEPT_NO FROM PERSON_ALL_MVIEW"
                                + " WHERE DEPT_NO LIKE :dept_0"
                                + " AND IS_ACTIVE IN ('A', 'T')";

            string cmd_body1 = " UNION"
                                + " SELECT DISTINCT DEPT_NO FROM PERSON_ALL_MVIEW"
                                + " WHERE DEPT_NO LIKE :dept_";

            string cmd_body2 = " AND IS_ACTIVE IN ('A', 'T')";

            string cmd_tail = ")";

            string cmd_all = cmd_header;

            if (company_and_firm_code.Count > 1)
            {
                for (int i = 1; i < company_and_firm_code.Count; i++)
                {
                    cmd_all += cmd_body1 + i.ToString() + cmd_body2;
                }
            }

            cmd_all += cmd_tail;

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            for (int i = 0; i < company_and_firm_code.Count; i++)
            {
                cmd.Parameters.Add("dept_" + i.ToString(), OracleType.VarChar).Value = company_and_firm_code[i] + "%";
            }

            List<Department> Departments = new List<Department>();
            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Department d = new Department();
                d.Name = Convert.ToString(reader[0]);     //以後可改成中文部門名稱
                d.Code = Convert.ToString(reader[0]);

                //判斷使用者權限, 取得人員詳細資料或削減後的資料
                if (CanSeePersonDetailInfo)
                    d.People = GetPeopleAllDataByDepartment(company_and_firm_code, Convert.ToString(reader[0]), PersonAllList, DesktopPhoneList);
                else
                    d.People = GetPeopleReduceDataByDepartment(company_and_firm_code, Convert.ToString(reader[0]), PersonAllList, DesktopPhoneList);

                Departments.Add(d);
            }
            reader.Dispose();
            connOracle.Dispose();

            //增加外派部門"Abroad"
            Department AbroadDepartment = new Department();
            AbroadDepartment.Name = "Abroad";
            AbroadDepartment.Code = "Abroad";
            AbroadDepartment.People = GetAbroadPeopleByCompany(m.AbroadUnit, AbroadPeopleList, DesktopPhoneList);
            if (AbroadDepartment.People.Count > 0)
                Departments.Add(AbroadDepartment);

            //增加不是人的部門"Others"
            Department OthersDepartment = new Department();
            OthersDepartment.Name = "Others";
            OthersDepartment.Code = "Others";
            OthersDepartment.NonPeople = GetOthersByCompany(m.LocationName, CompanyAreaMappingList, DesktopPhoneList);
            if (OthersDepartment.NonPeople.Count > 0)
                Departments.Add(OthersDepartment);

            return Departments;
        }

        /// <summary>取得公司代碼(DeptNo前兩碼)列表(從Oracle取得, 存到Memory中分類)</summary>
        public static List<CompanyFirmMapping> GetCompanyFirmMappingList()
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());

            string cmd_all = "SELECT LOCATION_NAME, COMPANY_AND_FIRM_CODE, ABROAD_UNIT"
                            + " FROM COMPANY_FIRM_MAPPING"
                            + " WHERE ENABLE = 'TRUE'"
                            + " ORDER BY ID";

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);

            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();
            List<string> LocationNameTemp = new List<string>();
            List<string> CompanyFirmCodeTemp = new List<string>();
            List<string> AbroadUnitTemp = new List<string>();

            while (reader.Read())
            {
                LocationNameTemp.Add(Convert.ToString(reader["LOCATION_NAME"]));
                CompanyFirmCodeTemp.Add(Convert.ToString(reader["COMPANY_AND_FIRM_CODE"]));
                AbroadUnitTemp.Add(Convert.ToString(reader["ABROAD_UNIT"]));
            }
            reader.Dispose();
            connOracle.Dispose();

            List<CompanyFirmMapping> CompanyFirmMappingList = new List<CompanyFirmMapping>();

            for (int i = 0; i < LocationNameTemp.Count; i++)
            {
                CompanyFirmMapping cfm = new CompanyFirmMapping();
                List<string> CompanyFirmCode = new List<string>();
                CompanyFirmCode.Add(CompanyFirmCodeTemp[i]);

                if (i == 0) //i == 0 時無法跟 i - 1 比較
                {
                    cfm.LocationName = LocationNameTemp[i];         //stirng
                    cfm.CompanyFirmCode = CompanyFirmCode;    //List<string>
                    cfm.AbroadUnit = AbroadUnitTemp[i];             //string
                    CompanyFirmMappingList.Add(cfm);
                }
                else  //與i - 1 比較
                {
                    //如果 i - 1 的LocationName == i的LocationName時
                    //把i - 1的 List<string> CompanyFirmCode取出,
                    //加入i = 0 的CompanyFirmCode
                    if (LocationNameTemp[i] == LocationNameTemp[i - 1])
                    {
                        CompanyFirmMappingList[CompanyFirmMappingList.Count - 1].CompanyFirmCode.Add(CompanyFirmCodeTemp[i]);
                    }
                    else
                    {
                        cfm.LocationName = LocationNameTemp[i];         //stirng
                        cfm.CompanyFirmCode = CompanyFirmCode;    //List<string>
                        cfm.AbroadUnit = AbroadUnitTemp[i];             //string
                        CompanyFirmMappingList.Add(cfm);
                    }
                }
            }

            return CompanyFirmMappingList;
        }

        ///<summary>取得公司區域號碼(AreaCode)列表</summary>
        public static List<CompanyAreacodeMapping> GetCompanyAreaMappingList()
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT LOCATION_NAME, AREA_CODE"
                           + " FROM COMPANY_AREA_MAPPING"
                           + " WHERE ENABLE = 'TRUE'"
                           + " AND AREA_CODE IS NOT NULL"
                           + " ORDER BY ID";
            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            List<string> LocationNameTemp = new List<string>();
            List<string> AreaCodeTemp = new List<string>();

            while (reader.Read())
            {
                LocationNameTemp.Add(Convert.ToString(reader["LOCATION_NAME"]));
                AreaCodeTemp.Add(Convert.ToString(reader["AREA_CODE"]));
            }
            reader.Dispose();
            connOracle.Dispose();

            List<CompanyAreacodeMapping> CompanyAreaMappingList = new List<CompanyAreacodeMapping>();

            for (int i = 0; i < LocationNameTemp.Count; i++)
            {
                CompanyAreacodeMapping cam = new CompanyAreacodeMapping();
                List<string> AreaCode = new List<string>();
                AreaCode.Add(AreaCodeTemp[i]);

                if (i == 0) //i == 0 時無法跟 i - 1 比較
                {
                    cam.LocationName = LocationNameTemp[i];         //stirng
                    cam.AreaCode = AreaCode;                        //List<string>
                    CompanyAreaMappingList.Add(cam);
                }
                else  //與i - 1 比較
                {
                    //如果 i - 1 的LocationName == i的LocationName時
                    //把i - 1的 List<string> AreaCode取出,
                    //加入i = 0 的CompanyAieaCode
                    if (LocationNameTemp[i] == LocationNameTemp[i - 1])
                    {
                        CompanyAreaMappingList[CompanyAreaMappingList.Count - 1].AreaCode.Add(AreaCodeTemp[i]);
                    }
                    else
                    {
                        cam.LocationName = LocationNameTemp[i];         //stirng
                        cam.AreaCode = AreaCode;                        //List<string>
                        CompanyAreaMappingList.Add(cam);
                    }
                }
            }

            return CompanyAreaMappingList;
        }

        /// <summary>取得特定公司別的外派人員詳細資料(從Memory)</summary>
        public static List<Person> GetAbroadPeopleByCompany(string abroad_unit, List<Person> AbroadPeopleList, List<DesktopPhoneCompare> DesktopPhoneList)
        {
            List<Person> ReturnAbroadPeople = new List<Person>();

            foreach (Person p in AbroadPeopleList)
            {
                if (p.ABROAD_NAME == abroad_unit)
                {
                    p.DESKTOP_PHONE = GetDesktopPhoneByPPSCode(p.PPS_CODE, DesktopPhoneList);
                    ReturnAbroadPeople.Add(p);
                }
            }

            return ReturnAbroadPeople;
        }

        /// <summary>取得不是人的分機資訊(從Memory)</summary>
        public static List<NonPerson> GetOthersByCompany(string location_name, List<CompanyAreacodeMapping> CompanyAreaMappingList, List<DesktopPhoneCompare> DesktopPhoneList)
        {
            //用location_name在CompanyAreaMappingList找到AreaCode,
            //用AreaCode在DesktopPhoneList找到PPS_CODE為空的資料,
            //組成ReturnNonPeopleList(包含FULL_NAME, AREA_CODE, LOCAL_NUMBER)回傳
            List<NonPerson> ReturnNonPeopleList = new List<NonPerson>();

            foreach (CompanyAreacodeMapping cam in CompanyAreaMappingList)
            {
                if (location_name == cam.LocationName)
                {
                    foreach (DesktopPhoneCompare dp in DesktopPhoneList)
                    {
                        if (cam.AreaCode.Contains(dp.AREA_CODE) && string.IsNullOrEmpty(dp.PPS_CODE))
                        {
                            NonPerson np = new NonPerson();
                            np.NAME = dp.FULL_NAME;
                            np.DESKTOP_PHONE = new DesktopPhone()
                            {
                                AREA_CODE = dp.AREA_CODE,
                                LOCAL_NUMBER = dp.LOCAL_NUMBER
                            };

                            ReturnNonPeopleList.Add(np);
                        }
                    }
                }
            }

            return ReturnNonPeopleList;
        }

        /// <summary>依照部門別取得人員所有詳細資料(從Memory)</summary>
        public static List<Person> GetPeopleAllDataByDepartment(List<string> company_and_firm_code, string department, List<Person> PersonAllList, List<DesktopPhoneCompare> DesktopPhoneList)
        {
            List<Person> ReturnPerson = new List<Person>();

            foreach (Person pa in PersonAllList)
                foreach (string s in company_and_firm_code)
                    if (pa.DEPT_NO.Substring(0, 2) == s)
                        if (pa.DEPT_NO.Substring(2, 2) == department)
                        {
                            pa.DESKTOP_PHONE = GetDesktopPhoneByPPSCode(pa.PPS_CODE, DesktopPhoneList);
                            ReturnPerson.Add(pa);
                        }

            return ReturnPerson;
        }

        /// <summary>依照部門別取得人員削減的詳細資料(從Memory)</summary>
        public static List<Person> GetPeopleReduceDataByDepartment(List<string> company_and_firm_code, string department, List<Person> PersonAllList, List<DesktopPhoneCompare> DesktopPhoneList)
        {
            List<Person> ReturnPerson = new List<Person>();

            foreach (Person pa in PersonAllList)
                foreach (string s in company_and_firm_code)
                    if (pa.DEPT_NO.Substring(0, 2) == s)
                        if (pa.DEPT_NO.Substring(2, 2) == department)
                        {
                            //削減人員詳細資料
                            pa.ADDRESS = null;
                            pa.MOBILE_EMP = null;
                            pa.TEL_NO = null;

                            pa.DESKTOP_PHONE = GetDesktopPhoneByPPSCode(pa.PPS_CODE, DesktopPhoneList);
                            ReturnPerson.Add(pa);
                        }

            return ReturnPerson;
        }

        /// <summary>依照員工編號取得桌機號碼(從Memory)</summary>
        public static List<DesktopPhone> GetDesktopPhoneByPPSCode(string pps_code, List<DesktopPhoneCompare> DesktopPhoneList)
        {
            List<DesktopPhone> ReturnDesktopPhoneList = new List<DesktopPhone>();

            foreach (DesktopPhoneCompare dpc in DesktopPhoneList)
            {
                if (dpc.PPS_CODE == pps_code)
                {
                    DesktopPhone d = new DesktopPhone();
                    d.AREA_CODE = dpc.AREA_CODE;
                    d.LOCAL_NUMBER = dpc.LOCAL_NUMBER;

                    ReturnDesktopPhoneList.Add(d);
                }
            }

            return ReturnDesktopPhoneList;
        }

        /// <summary>取得所有人員資料(從Oracle)</summary>
        public static List<Person> GetPersonAll(bool CanSeeA1, bool CanSeePersonDetailInfo)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());

            string cmd_all = null;

            //一般版本
            string cmd_withA1 = "SELECT PPS_CODE, NAME, EMPLOYMENT_DATE, DEPT_NO, DUTY_CODE, DUTY_NAME, IS_ACTIVE, ADDRESS, TEL_NO, MOBILE_EMP, EMAILID_2"
                            + " FROM PERSON_ALL_MVIEW"
                            + " WHERE IS_ACTIVE IN ('A','T')"
                            + " AND ABROAD_NAME IS NULL"
                            + " ORDER BY DUTY_CODE, EMPLOYMENT_DATE, PPS_CODE";

            //遮蔽A1管理部版本
            string cmd_withoutA1 = "SELECT PPS_CODE, NAME, EMPLOYMENT_DATE, DEPT_NO, DUTY_CODE, DUTY_NAME, IS_ACTIVE, ADDRESS, TEL_NO, MOBILE_EMP, EMAILID_2"
                            + " FROM PERSON_ALL_MVIEW"
                            + " WHERE IS_ACTIVE IN ('A','T')"
                            + " AND ABROAD_NAME IS NULL"
                            + " AND DEPT_NO <> '11A1A1'"
                            + " AND DEPT_NO <> '22A1A1'"
                            + " ORDER BY DUTY_CODE, EMPLOYMENT_DATE, PPS_CODE";

            if (CanSeeA1)
                cmd_all = cmd_withA1;
            else
                cmd_all = cmd_withoutA1;

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);

            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            List<Person> ReturnPersonAllList = new List<Person>();

            //Detail版本
            if (CanSeePersonDetailInfo)
            {
                while (reader.Read())
                {
                    Person p = new Person
                    {
                        PPS_CODE = Convert.ToString(reader["PPS_CODE"]),
                        NAME = Convert.ToString(reader["NAME"]),
                        DEPT_NO = Convert.ToString(reader["DEPT_NO"]),
                        DUTY_NAME = Convert.ToString(reader["DUTY_NAME"]),
                        ADDRESS = Convert.ToString(reader["ADDRESS"]),
                        TEL_NO = Convert.ToString(reader["TEL_NO"]),
                        MOBILE_EMP = Convert.ToString(reader["MOBILE_EMP"]),
                        EMAILID_2 = Convert.ToString(reader["EMAILID_2"])
                    };

                    ReturnPersonAllList.Add(p);
                }
            }
            else
            {
                while (reader.Read())
                {
                    Person p = new Person
                    {
                        PPS_CODE = Convert.ToString(reader["PPS_CODE"]),
                        NAME = Convert.ToString(reader["NAME"]),
                        DEPT_NO = Convert.ToString(reader["DEPT_NO"]),
                        DUTY_NAME = Convert.ToString(reader["DUTY_NAME"]),
                        //ADDRESS = Convert.ToString(reader["ADDRESS"]),
                        //TEL_NO = Convert.ToString(reader["TEL_NO"]),
                        //MOBILE_EMP = Convert.ToString(reader["MOBILE_EMP"]),
                        EMAILID_2 = Convert.ToString(reader["EMAILID_2"])
                    };

                    ReturnPersonAllList.Add(p);
                }
            }
            reader.Dispose();
            connOracle.Dispose();

            return ReturnPersonAllList;
        }

        /// <summary>取得所有外派人員資料(從Oracle)</summary>
        public static List<Person> GetAbroadPeople()
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT PPS_CODE, NAME, EMPLOYMENT_DATE, DEPT_NO, DUTY_CODE, DUTY_NAME, IS_ACTIVE, ABROAD_NAME, ADDRESS, TEL_NO, MOBILE_EMP, EMAILID_2"
                       + " FROM PERSON_ALL_MVIEW"
                       + " WHERE IS_ACTIVE IN ('A', 'T')"
                       + " AND ABROAD_NAME IS NOT NULL"
                       + " ORDER BY ABROAD_NAME, DUTY_CODE, EMPLOYMENT_DATE, PPS_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            List<Person> ReturnAbroadPeopleList = new List<Person>();

            while (reader.Read())
            {
                Person p = new Person
                {
                    PPS_CODE = Convert.ToString(reader["PPS_CODE"]),
                    NAME = Convert.ToString(reader["NAME"]),
                    DEPT_NO = Convert.ToString(reader["DEPT_NO"]),
                    DUTY_NAME = Convert.ToString(reader["DUTY_NAME"]),
                    ABROAD_NAME = Convert.ToString(reader["ABROAD_NAME"]),
                    ADDRESS = Convert.ToString(reader["ADDRESS"]),
                    TEL_NO = Convert.ToString(reader["TEL_NO"]),
                    MOBILE_EMP = Convert.ToString(reader["MOBILE_EMP"]),
                    EMAILID_2 = Convert.ToString(reader["EMAILID_2"])
                };

                ReturnAbroadPeopleList.Add(p);
            }
            reader.Dispose();
            connOracle.Dispose();

            return ReturnAbroadPeopleList;
        }

        /// <summary>取得所有桌機號碼(從Oracle)</summary>
        public static List<DesktopPhoneCompare> GetDesktopPhoneList()
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT FULL_NAME, AREA_CODE, LOCAL_NUMBER, PPS_CODE"
                           + " FROM PHONE_BOOK"
                           + " ORDER BY FULL_NAME, LOCAL_NUMBER";

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            List<DesktopPhoneCompare> DesktopPhoneList = new List<DesktopPhoneCompare>();

            while (reader.Read())
            {
                DesktopPhoneCompare dpc = new DesktopPhoneCompare
                {
                    FULL_NAME = Convert.ToString(reader["FULL_NAME"]),
                    AREA_CODE = Convert.ToString(reader["AREA_CODE"]),
                    LOCAL_NUMBER = Convert.ToString(reader["LOCAL_NUMBER"]),
                    PPS_CODE = Convert.ToString(reader["PPS_CODE"])
                };

                DesktopPhoneList.Add(dpc);
            }
            reader.Dispose();
            connOracle.Dispose();

            return DesktopPhoneList;
        }

        /// <summary>判斷是否有通訊錄Admin權限</summary>
        public static bool HasAdminRight(string pps_code)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT USER_ID"
                            + " FROM ADDRESSBOOK_ADMIN_RIGHT"
                            + " WHERE USER_TYPE = 'PPS'"
                            + " AND USER_ID = :PPS_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = pps_code;

            connOracle.Open();
            if (String.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
            {
                connOracle.Dispose();
                return false;
            }
            else
            {
                connOracle.Dispose();
                return true;
            }
        }

        /// <summary>判斷是否有通訊錄All_Mobile權限</summary>
        public static bool HasAllMobileRight(string pps_code)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT USER_ID"
                            + " FROM ADDRESSBOOK_ALL_MOBILE_RIGHT"
                            + " WHERE USER_TYPE = 'PPS'"
                            + " AND USER_ID = :PPS_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = pps_code;

            connOracle.Open();
            if (String.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
            {
                connOracle.Dispose();
                return false;
            }
            else
            {
                connOracle.Dispose();
                return true;
            }
        }

        public class Location
        {
            public string Name { get; set; }    //名稱
            public List<string> Code { get; set; }    //代號
            public List<Department> Departments { get; set; }
        }

        public class Department
        {
            public string Name { get; set; }    //名稱
            public string Code { get; set; }    //代號
            public List<Person> People { get; set; }        //人們
            public List<NonPerson> NonPeople { get; set; }  //不是人們(會議室、地點...)
        }

        public class Person
        {
            public string NAME { get; set; }
            public string PPS_CODE { get; set; }
            public string DEPT_NO { get; set; }
            public string DUTY_NAME { get; set; }
            public string ABROAD_NAME { get; set; }
            public string ADDRESS { get; set; }     //可削減
            public string TEL_NO { get; set; }      //可削減
            public string MOBILE_EMP { get; set; }  //可削減
            public string EMAILID_2 { get; set; }

            public List<DesktopPhone> DESKTOP_PHONE { get; set; }
            //public List<string> SIP_NUM { get; set; }
        }

        public class NonPerson
        {
            public string NAME { get; set; }    //名稱
            public DesktopPhone DESKTOP_PHONE { get; set; }
        }

        public class DesktopPhone
        {
            public string AREA_CODE { get; set; }
            public string LOCAL_NUMBER { get; set; }
        }

        public class DesktopPhoneCompare
        {
            public string FULL_NAME { get; set; }
            public string AREA_CODE { get; set; }
            public string LOCAL_NUMBER { get; set; }
            public string PPS_CODE { get; set; }
        }

        public class CompanyFirmMapping
        {
            public string LocationName { get; set; }
            public List<string> CompanyFirmCode { get; set; }
            public string AbroadUnit { get; set; }
        }

        public class CompanyAreacodeMapping
        {
            public string LocationName { get; set; }
            public List<string> AreaCode { get; set; }
        }

        /// <summary>取得Tier = Location清單</summary>
        public static List<LocationList> GetLocationList()
        {
            List<CompanyFirmMapping> CompanyFirmList = GetCompanyFirmMappingList();
            List<LocationList> ReturnLocationList = new List<LocationList>();

            foreach (CompanyFirmMapping cfm in CompanyFirmList)
            {
                LocationList l = new LocationList();
                l.L_Name = cfm.LocationName;
                string CompanyCode = null;

                foreach (string s in cfm.CompanyFirmCode)
                    CompanyCode += s + ",";

                l.L_Code = CompanyCode.Substring(0, CompanyCode.Length - 1);

                ReturnLocationList.Add(l);
            }

            return ReturnLocationList;
        }

        /// <summary>取得Tier = Departments清單</summary>
        /// <param name="CanSeeA1">可否看見管理部的權限(True, False)</param>
        /// <param name="L_Code">地點代碼</param>
        /// <returns>Tier = Departments清單</returns>
        public static List<DepartmentsList> GetDepartmentsList(Right UserRight, string L_Code)
        {
            string[] L_Code_List = L_Code.Split(',');

            //【01-1】取得正常部門，組合查詢字串
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_header = "SELECT DISTINCT substr(DEPT_NO,3,2) FROM ("
                                + " SELECT DISTINCT DEPT_NO FROM PERSON_ALL_MVIEW"
                                + " WHERE DEPT_NO LIKE :dept_0"
                                + " AND IS_ACTIVE IN ('A', 'T')";

            string cmd_body1 = " UNION"
                                + " SELECT DISTINCT DEPT_NO FROM PERSON_ALL_MVIEW"
                                + " WHERE DEPT_NO LIKE :dept_";

            string cmd_body2 = " AND IS_ACTIVE IN ('A', 'T')";

            string cmd_tail1 = ")";

            string cmd_tail2 = " WHERE substr(DEPT_NO,3,2) <> 'A1'";

            string cmd_all = cmd_header;

            if (L_Code_List.Length > 1)
            {
                for (int i = 1; i < L_Code_List.Length; i++)
                {
                    cmd_all += cmd_body1 + i.ToString() + cmd_body2;
                }
            }

            cmd_all += cmd_tail1;

            if (L_Code == "11,22")   //只遮蔽台北總公司的A1
            {
                if (!UserRight.CanSeeA1)      //檢查A1權限
                    cmd_all += cmd_tail2;
            }

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            for (int i = 0; i < L_Code_List.Length; i++)
                cmd.Parameters.Add("dept_" + i.ToString(), OracleType.VarChar).Value = L_Code_List[i] + "%";

            //【01-2】撈回部門資料
            List<DepartmentsList> DepartmentsList = new List<DepartmentsList>();
            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                DepartmentsList d = new DepartmentsList();
                d.L_Code = L_Code;
                d.D_Code = Convert.ToString(reader[0]);
                d.D_Name = GetDepartmentName(Convert.ToString(reader[0]));
                DepartmentsList.Add(d);
            }
            reader.Dispose();
            connOracle.Dispose();

            //【02-1】增加外派部門"Abroad"
            DepartmentsList AbroadDepartment = new DepartmentsList();
            AbroadDepartment.L_Code = L_Code;
            AbroadDepartment.D_Name = GetDepartmentName("ABROAD");
            AbroadDepartment.D_Code = "Abroad";
            DepartmentsList.Add(AbroadDepartment);

            //【02-2】增加不是人的部門"Others"
            DepartmentsList OthersDepartment = new DepartmentsList();
            OthersDepartment.L_Code = L_Code;
            OthersDepartment.D_Name = GetDepartmentName("OTHERS");
            OthersDepartment.D_Code = "Others";
            DepartmentsList.Add(OthersDepartment);

            //【99】回傳結果
            return DepartmentsList;
        }

        /// <summary>取得Tier = PeopelOrNonPeople中的People清單</summary>
        /// <param name="L_Code">地點代碼</param>
        /// <param name="D_Code">部門代碼</param>
        /// <returns>Tier = PeopelOrNonPeople中的People清單</returns>
        public static List<PeopleList> GetPeopleList(string L_Code, string D_Code)
        {
            string[] L_Code_List = L_Code.Split(',');

            //【01】取得部門人員列表，組合查詢字串
            OracleConnection connOracle = new OracleConnection(GetConnStrSipContact());

            string cmd_head = "SELECT PPS_CODE, NAME"
                            + " FROM PERSON_ALL_MVIEW"
                            + " WHERE IS_ACTIVE IN ('A','T')"
                //+ " AND ABROAD_NAME IS NULL"  //因應2015/1/1 增加Z1部門, 讓Z1部門的人顯示在原公司【Z1部】與外派公司【外派】
                            + " AND substr(DEPT_NO,1,2) IN ";

            string cmd_body1 = "(:company_no_0";
            string cmd_body2 = ", :company_no_";

            string cmd_tail = ")"
                            + " AND substr(DEPT_NO,3,2) = :dept_no"
                            + " ORDER BY DUTY_CODE, EMPLOYMENT_DATE, PPS_CODE";

            string cmd_all = null;

            if (L_Code_List.Length > 1)
            {
                cmd_all += cmd_head + cmd_body1;
                for (int i = 1; i < L_Code_List.Length; i++)
                {
                    cmd_all += cmd_body2 + i.ToString();
                }
                cmd_all += cmd_tail;
            }
            else
            {
                cmd_all = cmd_head + cmd_body1 + cmd_tail;
            }

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            for (int i = 0; i < L_Code_List.Length; i++)
            {
                cmd.Parameters.Add(":company_no_" + i.ToString(), OracleType.VarChar).Value = L_Code_List[i];
            }
            cmd.Parameters.Add(":dept_no", OracleType.VarChar).Value = D_Code;

            connOracle.Open();
            OracleDataReader reader = cmd.ExecuteReader();

            List<PeopleList> ReturnPeopleList = new List<PeopleList>();
            while (reader.Read())
            {
                PeopleList p = new PeopleList();
                p.L_Code = L_Code;
                p.D_Code = D_Code;
                p.P_Name = GetErrataPersonName(Convert.ToString(reader["NAME"]), Errata.CorrectName);
                p.PPS_Code = Convert.ToString(reader["PPS_CODE"]);

                ReturnPeopleList.Add(p);
            }
            reader.Dispose();
            connOracle.Dispose();

            return ReturnPeopleList;
        }

        /// <summary>取得外派人員清單</summary>
        /// <param name="L_Code">地點代碼</param>
        /// <param name="D_Code">部門代碼</param>
        /// <returns>外派人員清單</returns>
        public static List<PeopleList> GetAbroadPeopleList(string L_Code, string D_Code)
        {
            string[] L_Code_List = L_Code.Split(',');
            List<string> AbroadUnit = new List<string>();
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            ConnOracle.Open();

            //【01】找出Abroad_Unit
            for (int i = 0; i < L_Code_List.Length; i++)
            {
                string cmd_all = "SELECT ABROAD_UNIT"
                               + " FROM COMPANY_FIRM_MAPPING"
                               + " WHERE ENABLE = 'TRUE'"
                               + " AND COMPANY_AND_FIRM_CODE = :company_and_firm_code";
                OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
                cmd.Parameters.Add(":company_and_firm_code", OracleType.VarChar).Value = L_Code_List[i];

                AbroadUnit.Add(Convert.ToString(cmd.ExecuteScalar()));
            }

            //【02】找出外派人員
            List<PeopleList> ReturnPeopleList = new List<PeopleList>();
            for (int i = 0; i < AbroadUnit.Count; i++)
            {
                string cmd_all = "SELECT NAME, PPS_CODE"
                               + " FROM PERSON_ALL_MVIEW"
                               + " WHERE IS_ACTIVE IN ('A' ,'T')"
                               + " AND ABROAD_NAME = :abroad_name"
                               + " ORDER BY DUTY_CODE, EMPLOYMENT_DATE, PPS_CODE";
                OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
                cmd.Parameters.Add(":abroad_name", OracleType.VarChar).Value = AbroadUnit[i];
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    PeopleList p = new PeopleList();
                    p.L_Code = L_Code;
                    p.D_Code = D_Code;
                    p.P_Name = GetErrataPersonName(Convert.ToString(reader["NAME"]), Errata.CorrectName);
                    p.PPS_Code = Convert.ToString(reader["PPS_CODE"]);

                    ReturnPeopleList.Add(p);
                }
                reader.Dispose();
            }
            ConnOracle.Dispose();

            return ReturnPeopleList;
        }

        /// <summary>外派據點轉換成通訊錄2.0據點</summary>
        public static string GetLocationName(string AbroadName)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());

            //【01】找出Abroad_Unit
            string cmd_all = "SELECT LOCATION_NAME"
                           + " FROM COMPANY_FIRM_MAPPING"
                           + " WHERE ENABLE = 'TRUE'"
                           + " AND ABROAD_UNIT = :ABROAD_UNIT";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":ABROAD_UNIT", OracleType.VarChar).Value = AbroadName;

            ConnOracle.Open();
            string AbroadUnitName = Convert.ToString(cmd.ExecuteScalar());
            ConnOracle.Dispose();

            return AbroadUnitName;
        }

        /// <summary>取得Tier = PeopleOrNonPeople中的NonPeople清單</summary>
        /// <param name="L_Code">地點代碼</param>
        /// <param name="D_Code">部門代碼</param>
        /// <returns>Tier = PeopleOrNonPeople中的NonPeople清單</returns>
        public static List<NonPeopleList> GetNonPeopleList(string L_Code, string D_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());

            //【01】取得AreaCode
            string cmd_all = "SELECT AREA_CODE"
                           + " FROM COMPANY_AREA_MAPPING"
                           + " WHERE ENABLE = 'TRUE'"
                           + " AND L_CODE = :L_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":L_CODE", OracleType.VarChar).Value = L_Code;
            ConnOracle.Open();
            string AreaCode = Convert.ToString(cmd.ExecuteScalar());

            //【02】取得不是人們
            cmd_all = "SELECT FULL_NAME"
                    + " FROM PHONE_BOOK"
                    + " WHERE DISPLAY = 'Y'"
                    + " AND AREA_CODE = :AREA_CODE"
                    + " AND PPS_CODE IS NULL"
                    + " ORDER BY FULL_NAME, LOCAL_NUMBER";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add("AREA_CODE", OracleType.VarChar).Value = AreaCode;

            OracleDataReader reader = cmd.ExecuteReader();
            List<NonPeopleList> ReturnNonPeopleList = new List<NonPeopleList>();
            while (reader.Read())
            {
                NonPeopleList np = new NonPeopleList();
                np.L_Code = L_Code;
                np.D_Code = D_Code;
                np.N_Name = Convert.ToString(reader["FULL_NAME"]);

                ReturnNonPeopleList.Add(np);
            }
            reader.Dispose();
            ConnOracle.Dispose();

            return ReturnNonPeopleList;
        }

        /// <summary>取得人員詳細資訊</summary>
        /// <param name="L_Code">地點代碼</param>
        /// <param name="D_Code">部門代碼</param>
        /// <param name="PPS_Code">員工編號</param>
        /// <param name="CanSeePersonEmpInfo">可否看見人員EMP資料的權限(True, False)</param>
        /// <returns>人員詳細資訊</returns>
        public static PeopleDetailInfo GetPeopleDetailInfo(string L_Code, string D_Code, string Target_PPS_Code, SipUserInfo UserInfo, Right UserRight)
        {
            //【01】取得ERP中人事資料個人資訊
            PeopleDetailInfo ReturnPeopleDetailInfo = new PeopleDetailInfo();

            ReturnPeopleDetailInfo.L_Code = L_Code;
            ReturnPeopleDetailInfo.D_Code = D_Code;

            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT NAME, ABROAD_NAME, DUTY_NAME, EMAILID_2, MOBILE_EMP, TEL_NO, ADDRESS, CONTACT, MOBILE_CONTACT, TEL_NO_CONTACT, ADDRESS_CONTACT, ABROAD_MOBILE"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE PPS_CODE = :PPS_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = Target_PPS_Code;
            ConnOracle.Open();

            OracleDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                ReturnPeopleDetailInfo.P_Name = GetErrataPersonName(Convert.ToString(reader["NAME"]), Errata.CorrectName);

                if (!string.IsNullOrEmpty(Convert.ToString(reader["ABROAD_NAME"])))
                    ReturnPeopleDetailInfo.Abroad_To = GetLocationName(Convert.ToString(reader["ABROAD_NAME"]));

                ReturnPeopleDetailInfo.Duty_Name = Convert.ToString(reader["DUTY_NAME"]);
                ReturnPeopleDetailInfo.Email = Convert.ToString(reader["EMAILID_2"]);

                if (CheckRight(RightItems.MOBILE_EMP_RIGHT, L_Code, D_Code, Target_PPS_Code, UserInfo, UserRight))
                {
                    ReturnPeopleDetailInfo.Mobile = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["MOBILE_EMP"]));
                    ReturnPeopleDetailInfo.Abroad_Mobile = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["ABROAD_MOBILE"]));
                }
                if (CheckRight(RightItems.EMPDATA_RIGHT, L_Code, D_Code, Target_PPS_Code, UserInfo, UserRight))
                {
                    ReturnPeopleDetailInfo.Mobile = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["MOBILE_EMP"]));
                    ReturnPeopleDetailInfo.Abroad_Mobile = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["ABROAD_MOBILE"]));
                    ReturnPeopleDetailInfo.Tel = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["TEL_NO"]));
                    ReturnPeopleDetailInfo.Address = Convert.ToString(reader["ADDRESS"]);
                    ReturnPeopleDetailInfo.Contact = Convert.ToString(reader["CONTACT"]);
                    ReturnPeopleDetailInfo.Contact_Mobile = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["MOBILE_CONTACT"]));
                    ReturnPeopleDetailInfo.Contact_Tel = ChrisKit.CleanPhoneNumReturnNull(Convert.ToString(reader["TEL_NO_CONTACT"]));
                    ReturnPeopleDetailInfo.Contact_Address = Convert.ToString(reader["ADDRESS_CONTACT"]);
                }
            }
            reader.Dispose();

            //【02】取得PHONE_BOOK中桌機資料
            cmd_all = "SELECT AREA_CODE, LOCAL_NUMBER"
                    + " FROM PHONE_BOOK"
                    + " WHERE PPS_CODE = :PPS_CODE"
                    + " ORDER BY AREA_CODE, LOCAL_NUMBER";
            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = Target_PPS_Code;

            reader = cmd.ExecuteReader();
            List<DesktopPhoneList> DesktopPhoneList = new List<DesktopPhoneList>();
            while (reader.Read())
            {
                DesktopPhoneList dp = new DesktopPhoneList();
                dp.Area_Code = Convert.ToString(reader["AREA_CODE"]);
                dp.Local_Number = Convert.ToString(reader["LOCAL_NUMBER"]);

                DesktopPhoneList.Add(dp);
            }
            reader.Dispose();
            ReturnPeopleDetailInfo.DesktopPhoneList = DesktopPhoneList;

            //【03】取得SIP_Num, Device_Type
            cmd_all = "SELECT SIP_NUM, DEVICETYPE"
                    + " FROM REG_USER"
                    + " WHERE STATUS = 'Y'"
                    + " AND PPS_CODE = :PPS_CODE"
                    + " ORDER BY SIP_NUM";
            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = Target_PPS_Code;
            reader = cmd.ExecuteReader();

            List<Sip> ReturnSipList = new List<Sip>();

            while (reader.Read())
            {
                Sip s = new Sip()
                {
                    SIP_Num = Convert.ToString(reader["SIP_NUM"]),
                    Device_Type = Convert.ToString(reader["DEVICETYPE"])
                };

                ReturnSipList.Add(s);
            }
            reader.Dispose();
            ConnOracle.Dispose();

            ReturnPeopleDetailInfo.SIP = ReturnSipList;

            return ReturnPeopleDetailInfo;
        }

        /// <summary>取得人員詳細資訊(不檢查權限, 目前給人員識別證使用)</summary>
        public static PeopleDetailInfo GetPeopleDetailInfo(string L_Code, string D_Code, string PPS_Code)
        {
            //【01】取得ERP中人事資料個人資訊
            PeopleDetailInfo ReturnPeopleDetailInfo = new PeopleDetailInfo();

            ReturnPeopleDetailInfo.L_Code = L_Code;
            ReturnPeopleDetailInfo.D_Code = D_Code;

            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT NAME, ABROAD_NAME, DUTY_NAME, EMAILID_2, MOBILE_EMP, TEL_NO, ADDRESS, CONTACT, MOBILE_CONTACT, TEL_NO_CONTACT, ADDRESS_CONTACT"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE PPS_CODE = :PPS_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;
            ConnOracle.Open();

            OracleDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                ReturnPeopleDetailInfo.P_Name = GetErrataPersonName(Convert.ToString(reader["NAME"]), Errata.CorrectName);

                if (!string.IsNullOrEmpty(Convert.ToString(reader["ABROAD_NAME"])))
                    ReturnPeopleDetailInfo.Abroad_To = GetLocationName(Convert.ToString(reader["ABROAD_NAME"]));

                ReturnPeopleDetailInfo.Duty_Name = Convert.ToString(reader["DUTY_NAME"]);
                ReturnPeopleDetailInfo.Email = Convert.ToString(reader["EMAILID_2"]);
                ReturnPeopleDetailInfo.Mobile = ChrisKit.CleanPhoneNum(Convert.ToString(reader["MOBILE_EMP"]));

                //EMP_DATA不顯示
                //ReturnPeopleDetailInfo.Tel = PhoneNumberFilter(Convert.ToString(reader["TEL_NO"]));
                //ReturnPeopleDetailInfo.Address = Convert.ToString(reader["ADDRESS"]);
                //ReturnPeopleDetailInfo.Contact = Convert.ToString(reader["CONTACT"]);
                //ReturnPeopleDetailInfo.Contact_Mobile = PhoneNumberFilter(Convert.ToString(reader["MOBILE_CONTACT"]));
                //ReturnPeopleDetailInfo.Contact_Tel = PhoneNumberFilter(Convert.ToString(reader["TEL_NO_CONTACT"]));
                //ReturnPeopleDetailInfo.Contact_Address = Convert.ToString(reader["ADDRESS_CONTACT"]);
            }
            reader.Dispose();

            //【02】取得PHONE_BOOK中桌機資料
            cmd_all = "SELECT AREA_CODE, LOCAL_NUMBER"
                    + " FROM PHONE_BOOK"
                    + " WHERE PPS_CODE = :PPS_CODE"
                    + " ORDER BY AREA_CODE, LOCAL_NUMBER";
            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;

            reader = cmd.ExecuteReader();
            List<DesktopPhoneList> DesktopPhoneList = new List<DesktopPhoneList>();
            while (reader.Read())
            {
                DesktopPhoneList dp = new DesktopPhoneList();
                dp.Area_Code = Convert.ToString(reader["AREA_CODE"]);
                dp.Local_Number = Convert.ToString(reader["LOCAL_NUMBER"]);

                DesktopPhoneList.Add(dp);
            }
            reader.Dispose();
            ReturnPeopleDetailInfo.DesktopPhoneList = DesktopPhoneList;

            //【03】取得SIP_Num, Device_Type
            cmd_all = "SELECT SIP_NUM, DEVICETYPE"
                    + " FROM REG_USER"
                    + " WHERE STATUS = 'Y'"
                    + " AND PPS_CODE = :PPS_CODE"
                    + " ORDER BY SIP_NUM";
            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;
            reader = cmd.ExecuteReader();

            List<Sip> ReturnSipList = new List<Sip>();

            while (reader.Read())
            {
                Sip s = new Sip()
                {
                    SIP_Num = Convert.ToString(reader["SIP_NUM"]),
                    Device_Type = Convert.ToString(reader["DEVICETYPE"])
                };

                ReturnSipList.Add(s);
            }
            reader.Dispose();
            ConnOracle.Dispose();

            ReturnPeopleDetailInfo.SIP = ReturnSipList;

            return ReturnPeopleDetailInfo;
        }

        /// <summary>取得非人類的詳細資訊</summary>
        /// <param name="L_Code">地點代碼</param>
        /// <param name="D_Code">部門代碼</param>
        /// <param name="N_Name">非人類名稱</param>
        /// <returns>非人類的詳細資訊</returns>
        public static NonPeopleDetailInfo GetNonPeopleDetailInfo(string L_Code, string D_Code, string N_Name)
        {
            //【01】取得據點代碼
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT AREA_CODE"
                           + " FROM COMPANY_AREA_MAPPING"
                           + " WHERE L_CODE = :L_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":L_CODE", OracleType.VarChar).Value = L_Code;
            ConnOracle.Open();

            string AreaCode = Convert.ToString(cmd.ExecuteScalar());

            //【02】取得PHONE_BOOK中桌機資料
            cmd_all = "SELECT LOCAL_NUMBER"
                    + " FROM PHONE_BOOK"
                    + " WHERE AREA_CODE = :AREA_CODE"
                    + " AND FULL_NAME = :FULL_NAME";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add("AREA_CODE", OracleType.VarChar).Value = AreaCode;
            cmd.Parameters.Add("FULL_NAME", OracleType.VarChar).Value = N_Name;

            List<DesktopPhoneList> ReturnDesktopPhoneList = new List<DesktopPhoneList>();
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DesktopPhoneList dp = new DesktopPhoneList();
                dp.Area_Code = AreaCode;
                dp.Local_Number = Convert.ToString(reader["LOCAL_NUMBER"]);

                ReturnDesktopPhoneList.Add(dp);
            }
            reader.Dispose();
            ConnOracle.Dispose();

            NonPeopleDetailInfo ReturnNonPeopleDetailInfo = new NonPeopleDetailInfo();
            ReturnNonPeopleDetailInfo.D_Code = D_Code;
            ReturnNonPeopleDetailInfo.L_Code = L_Code;
            ReturnNonPeopleDetailInfo.N_Name = N_Name;
            ReturnNonPeopleDetailInfo.DesktopPhoneList = ReturnDesktopPhoneList;

            return ReturnNonPeopleDetailInfo;
        }

        /// <summary>取得L_Code, D_Code</summary>
        /// <param name="DEPT_NO"></param>
        /// <returns>L_Code, D_Code</returns>
        public static AddressBookInfo GetAddressBookInfo(string DEPT_NO)
        {
            //DEPT_NO : 11MIMI, 11SLPOS 6~8碼
            string D_Code = DEPT_NO.Substring(2, 2);
            string CompanyAndFirmCode = DEPT_NO.Substring(0, 2);

            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT L_CODE"
                           + " FROM COMPANY_FIRM_MAPPING"
                           + " WHERE COMPANY_AND_FIRM_CODE = :COMPANY_AND_FIRM_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":COMPANY_AND_FIRM_CODE", OracleType.VarChar).Value = CompanyAndFirmCode;

            ConnOracle.Open();
            string L_Code = Convert.ToString(cmd.ExecuteScalar());
            ConnOracle.Dispose();

            AddressBookInfo ReturnAddressBookInfo = new AddressBookInfo
            {
                L_Code = L_Code,
                D_Code = D_Code
            };

            return ReturnAddressBookInfo;
        }

        /// <summary>取得使用者權限</summary>
        /// <param name="UserInfo">使用者Sip資訊</param>
        /// <returns>使用者權限</returns>
        public static Right GetUserRight(SipUserInfo UserInfo)
        {
            //【01】初始化權限物件
            Right UserRight = new Right();

            UserRight.EmpDataRight = new EmpDataRight();
            UserRight.MobileEmpRight = new MobileEmpRight();

            UserRight.EmpDataRight.DeptWhiteRight = new List<DeptRight>();
            UserRight.EmpDataRight.DeptBlackRight = new List<DeptRight>();
            UserRight.EmpDataRight.PersonWhiteRight = new List<PersonRight>();
            UserRight.EmpDataRight.PersonBlackRight = new List<PersonRight>();

            UserRight.MobileEmpRight.DeptWhiteRight = new List<DeptRight>();
            UserRight.MobileEmpRight.DeptBlackRight = new List<DeptRight>();
            UserRight.MobileEmpRight.PersonWhiteRight = new List<PersonRight>();
            UserRight.MobileEmpRight.PersonBlackRight = new List<PersonRight>();

            UserRight.CanSeeA1 = false;
            UserRight.IsAdmin = false;

            //【02】判斷是否有Admin權限
            if (HasAdminRight(UserInfo.PPS_CODE))
            {
                UserRight.IsAdmin = true;
                UserRight.CanSeeA1 = true;

                return UserRight;
            }
            else
            {
                //【03】判斷是否有All_Mobile權限
                if (HasAllMobileRight(UserInfo.PPS_CODE))
                    UserRight.IsAllMobile = true;

                //【04】取得此人的L_CODE, D_CODE
                AddressBookInfo UserAddressBookInfo = GetAddressBookInfo(UserInfo.DEPT_NO);

                //【05-1-MOBILE_EMP】判斷是否是部門最高主管, 取得所屬部門MOBILE_EMP的權限
                UserRight = GetDepartmentLeaderRight(UserAddressBookInfo, UserInfo, UserRight);

                //【05-2-EMPDATA】檢查手動權限表ADDRESSBOOK_EMPDATA_RIGHT
                UserRight = GetEmpDataDeptWhiteRight(RightItems.EMPDATA_RIGHT, UserAddressBookInfo, UserInfo, UserRight);     //白名單
                UserRight = GetEmpDataPersonWhiteRight(RightItems.EMPDATA_RIGHT, UserAddressBookInfo, UserInfo, UserRight);   //白名單
                UserRight = GetEmpDataDeptBlackRight(RightItems.EMPDATA_RIGHT, UserAddressBookInfo, UserInfo, UserRight);     //黑名單
                UserRight = GetEmpDataPersonBlackRight(RightItems.EMPDATA_RIGHT, UserAddressBookInfo, UserInfo, UserRight);   //黑名單

                //【05-3-MOBILE_EMP】檢查手動權限表ADDRESSBOOK_MOBILE_EMP_RIGHT
                UserRight = GetEmpDataDeptWhiteRight(RightItems.MOBILE_EMP_RIGHT, UserAddressBookInfo, UserInfo, UserRight);     //白名單
                UserRight = GetEmpDataPersonWhiteRight(RightItems.MOBILE_EMP_RIGHT, UserAddressBookInfo, UserInfo, UserRight);   //白名單
                UserRight = GetEmpDataDeptBlackRight(RightItems.MOBILE_EMP_RIGHT, UserAddressBookInfo, UserInfo, UserRight);     //黑名單
                UserRight = GetEmpDataPersonBlackRight(RightItems.MOBILE_EMP_RIGHT, UserAddressBookInfo, UserInfo, UserRight);   //黑名單

                //【05-4-A1】判斷是否是A1部門及台北公司, 取得A1權限
                UserRight = GetHQRight(UserAddressBookInfo, UserInfo, UserRight);

                //【05-5-A1】檢查手動權限表ADDRESSBOOK_A1_RIGHT
                UserRight = GetA1DataRight(UserAddressBookInfo, UserInfo, UserRight);

                return UserRight;
            }
        }

        /// <summary>判斷是否是部門最高主管，取得所屬部門MOBILE_EMP的權限</summary>
        /// <param name="UserAddressBookInfo">L_CODE, D_CODE</param>
        /// <param name="UserInfo">Sip UserInfo</param>
        /// <param name="UserRight">使用者權限</param>
        /// <returns>一個部門權限</returns>
        public static Right GetDepartmentLeaderRight(AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            string[] L_Code = UserAddressBookInfo.L_Code.Split(',');
            string D_Code = UserAddressBookInfo.D_Code;

            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_head = "SELECT * FROM "
                            + " ("
                                + " SELECT PPS_CODE"
                                + " FROM PERSON_ALL_MVIEW"
                                + " WHERE IS_ACTIVE IN ('A','T')"
                                + " AND substr(\"DEPT_NO\", 0, 2) IN (";
            string cmd_body = null;
            for (int i = 0; i < L_Code.Length; i++)
            {
                cmd_body += ":L_CODE_" + i.ToString() + ",";
            }
            cmd_body = cmd_body.Substring(0, cmd_body.Length - 1);

            string cmd_tail = " )"
                                + " AND substr(\"DEPT_NO\", 3, 2) IN (:D_CODE)"
                                + " AND ABROAD_NAME IS NULL"
                                + " ORDER BY DUTY_CODE, EMPLOYMENT_DATE, PPS_CODE"
                              + ") "
                              + " WHERE ROWNUM <= 1";
            string cmd_all = cmd_head + cmd_body + cmd_tail;

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            for (int i = 0; i < L_Code.Length; i++)
            {
                cmd.Parameters.Add(":L_CODE_" + i.ToString(), OracleType.VarChar).Value = L_Code[i];
            }
            cmd.Parameters.Add(":D_CODE", OracleType.VarChar).Value = D_Code;

            ConnOracle.Open();
            string DepartmentLeaderPpscode = Convert.ToString(cmd.ExecuteScalar());
            ConnOracle.Dispose();

            if (DepartmentLeaderPpscode == UserInfo.PPS_CODE)
            {
                DeptRight dr = new DeptRight()
               {
                   L_Code = UserAddressBookInfo.L_Code,
                   D_Code = UserAddressBookInfo.D_Code
               };

                if (!UserRight.MobileEmpRight.DeptWhiteRight.Contains(dr))
                    UserRight.MobileEmpRight.DeptWhiteRight.Add(dr);

                return UserRight;
            }
            else
            {
                return UserRight;
            }
        }

        /// <summary>取得手動權限表ADDRESSBOOK_EMPDATA_RIGHT中【部門】白名單權限</summary>
        /// <param name="UserInfo">Sip UserInfo</param>
        /// <param name="UserRight">使用者權限</param>
        /// <returns>使用者權限</returns>
        public static Right GetEmpDataDeptWhiteRight(RightItems RightItem, AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            DeptRight ReturnDeptRightList = new DeptRight();
            //【01】檢查User_ID:PPS  → Target_ID:DEPT的情況
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT TARGET_ID, TARGET_ID_2"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                           + " FROM ADDRESSBOOK_" + RightItem.ToString()
                           + " WHERE USER_ID = :USER_ID"
                           + " AND USER_TYPE = 'PPS'"
                           + " AND TARGET_TYPE = 'DEPT'"
                           + " AND IS_BLOCK = 'N'";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserInfo.PPS_CODE;
            ConnOracle.Open();

            OracleDataReader Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                DeptRight dr = new DeptRight()
                {
                    L_Code = Convert.ToString(Reader["TARGET_ID"]),
                    D_Code = Convert.ToString(Reader["TARGET_ID_2"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.DeptWhiteRight.Contains(dr))
                        UserRight.EmpDataRight.DeptWhiteRight.Add(dr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.DeptWhiteRight.Contains(dr))
                        UserRight.MobileEmpRight.DeptWhiteRight.Add(dr);
                }
            }
            Reader.Dispose();

            //【02】檢查User_ID:DEPT → Target_ID:DEPT的情況
            cmd_all = "SELECT TARGET_ID, TARGET_ID_2"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                    + " FROM ADDRESSBOOK_" + RightItem.ToString()
                    + " WHERE USER_ID = :USER_ID"
                    + " AND USER_ID_2 = :USER_ID_2"
                    + " AND USER_TYPE = 'DEPT'"
                    + " AND TARGET_TYPE = 'DEPT'"
                    + " AND IS_BLOCK = 'N'";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserAddressBookInfo.L_Code;
            cmd.Parameters.Add(":USER_ID_2", OracleType.VarChar).Value = UserAddressBookInfo.D_Code;

            Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                DeptRight dr = new DeptRight()
                {
                    L_Code = Convert.ToString(Reader["TARGET_ID"]),
                    D_Code = Convert.ToString(Reader["TARGET_ID_2"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.DeptWhiteRight.Contains(dr))
                        UserRight.EmpDataRight.DeptWhiteRight.Add(dr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.DeptWhiteRight.Contains(dr))
                        UserRight.MobileEmpRight.DeptWhiteRight.Add(dr);
                }
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            return UserRight;
        }

        /// <summary>取得手動權限表ADDRESSBOOK_EMPDATA_RIGHT中【人員】白名單權限</summary>
        /// <param name="UserInfo">Sip UserInfo</param>
        /// <param name="UserRight">使用者權限</param>
        /// <returns>使用者權限</returns>
        public static Right GetEmpDataPersonWhiteRight(RightItems RightItem, AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            DeptRight ReturnDeptRightList = new DeptRight();
            //【01】檢查User_ID:PPS  → Target_ID:PPS的情況
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT TARGET_ID"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                           + " FROM ADDRESSBOOK_" + RightItem.ToString()
                           + " WHERE USER_ID = :USER_ID"
                           + " AND USER_TYPE = 'PPS'"
                           + " AND TARGET_TYPE = 'PPS'"
                           + " AND IS_BLOCK = 'N'";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserInfo.PPS_CODE;
            ConnOracle.Open();

            OracleDataReader Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                PersonRight pr = new PersonRight()
                {
                    PPS_Code = Convert.ToString(Reader["TARGET_ID"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.PersonWhiteRight.Contains(pr))
                        UserRight.EmpDataRight.PersonWhiteRight.Add(pr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.PersonWhiteRight.Contains(pr))
                        UserRight.MobileEmpRight.PersonWhiteRight.Add(pr);
                }
            }
            Reader.Dispose();

            //【02】檢查User_ID:DEPT → Target_ID:PPS的情況
            cmd_all = "SELECT TARGET_ID"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                    + " FROM ADDRESSBOOK_" + RightItem.ToString()
                    + " WHERE USER_ID = :USER_ID"
                    + " AND USER_ID_2 = :USER_ID_2"
                    + " AND USER_TYPE = 'DEPT'"
                    + " AND TARGET_TYPE = 'PPS'"
                    + " AND IS_BLOCK = 'N'";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserAddressBookInfo.L_Code;
            cmd.Parameters.Add(":USER_ID_2", OracleType.VarChar).Value = UserAddressBookInfo.D_Code;

            Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                PersonRight pr = new PersonRight()
                {
                    PPS_Code = Convert.ToString(Reader["TARGET_ID"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.PersonWhiteRight.Contains(pr))
                        UserRight.EmpDataRight.PersonWhiteRight.Add(pr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.PersonWhiteRight.Contains(pr))
                        UserRight.MobileEmpRight.PersonWhiteRight.Add(pr);
                }
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            return UserRight;
        }

        /// <summary>取得手動權限表ADDRESSBOOK_EMPDATA_RIGHT中【部門】黑名單權限</summary>
        /// <param name="UserInfo">Sip UserInfo</param>
        /// <param name="UserRight">使用者權限</param>
        /// <returns>使用者權限</returns>
        public static Right GetEmpDataDeptBlackRight(RightItems RightItem, AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            DeptRight ReturnDeptRightList = new DeptRight();
            //【01】檢查User_ID:PPS  → Target_ID:DEPT的情況
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT TARGET_ID, TARGET_ID_2"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                           + " FROM ADDRESSBOOK_" + RightItem.ToString()
                           + " WHERE USER_ID = :USER_ID"
                           + " AND USER_TYPE = 'PPS'"
                           + " AND TARGET_TYPE = 'DEPT'"
                           + " AND IS_BLOCK = 'Y'";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserInfo.PPS_CODE;
            ConnOracle.Open();

            OracleDataReader Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                DeptRight dr = new DeptRight()
                {
                    L_Code = Convert.ToString(Reader["TARGET_ID"]),
                    D_Code = Convert.ToString(Reader["TARGET_ID_2"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.DeptBlackRight.Contains(dr))
                        UserRight.EmpDataRight.DeptBlackRight.Add(dr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.DeptBlackRight.Contains(dr))
                        UserRight.MobileEmpRight.DeptBlackRight.Add(dr);
                }
            }
            Reader.Dispose();

            //【02】檢查User_ID:DEPT → Target_ID:DEPT的情況
            cmd_all = "SELECT TARGET_ID, TARGET_ID_2"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                    + " FROM ADDRESSBOOK_" + RightItem.ToString()
                    + " WHERE USER_ID = :USER_ID"
                    + " AND USER_ID_2 = :USER_ID_2"
                    + " AND USER_TYPE = 'DEPT'"
                    + " AND TARGET_TYPE = 'DEPT'"
                    + " AND IS_BLOCK = 'Y'";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserAddressBookInfo.L_Code;
            cmd.Parameters.Add(":USER_ID_2", OracleType.VarChar).Value = UserAddressBookInfo.D_Code;

            Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                DeptRight dr = new DeptRight()
                {
                    L_Code = Convert.ToString(Reader["TARGET_ID"]),
                    D_Code = Convert.ToString(Reader["TARGET_ID_2"])
                };
                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.DeptBlackRight.Contains(dr))
                        UserRight.EmpDataRight.DeptBlackRight.Add(dr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.DeptBlackRight.Contains(dr))
                        UserRight.MobileEmpRight.DeptBlackRight.Add(dr);
                }
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            return UserRight;
        }

        /// <summary>取得手動權限表ADDRESSBOOK_EMPDATA_RIGHT中【人員】黑名單權限</summary>
        /// <param name="UserInfo">Sip UserInfo</param>
        /// <param name="UserRight">使用者權限</param>
        /// <returns>使用者權限</returns>
        public static Right GetEmpDataPersonBlackRight(RightItems RightItem, AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            DeptRight ReturnDeptRightList = new DeptRight();
            //【01】檢查User_ID:PPS  → Target_ID:PPS的情況
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT TARGET_ID"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                           + " FROM ADDRESSBOOK_" + RightItem.ToString()
                           + " WHERE USER_ID = :USER_ID"
                           + " AND USER_TYPE = 'PPS'"
                           + " AND TARGET_TYPE = 'PPS'"
                           + " AND IS_BLOCK = 'Y'";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserInfo.PPS_CODE;
            ConnOracle.Open();

            OracleDataReader Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                PersonRight pr = new PersonRight()
                {
                    PPS_Code = Convert.ToString(Reader["TARGET_ID"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.PersonBlackRight.Contains(pr))
                        UserRight.EmpDataRight.PersonBlackRight.Add(pr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.PersonBlackRight.Contains(pr))
                        UserRight.MobileEmpRight.PersonBlackRight.Add(pr);
                }
            }
            Reader.Dispose();

            //【02】檢查User_ID:DEPT → Target_ID:PPS的情況
            cmd_all = "SELECT TARGET_ID"
                //+ " FROM ADDRESSBOOK_EMPDATA_RIGHT"
                    + " FROM ADDRESSBOOK_" + RightItem.ToString()
                    + " WHERE USER_ID = :USER_ID"
                    + " AND USER_ID_2 = :USER_ID_2"
                    + " AND USER_TYPE = 'DEPT'"
                    + " AND TARGET_TYPE = 'PPS'"
                    + " AND IS_BLOCK = 'Y'";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserAddressBookInfo.L_Code;
            cmd.Parameters.Add(":USER_ID_2", OracleType.VarChar).Value = UserAddressBookInfo.D_Code;

            Reader = cmd.ExecuteReader();
            while (Reader.Read())
            {
                PersonRight pr = new PersonRight()
                {
                    PPS_Code = Convert.ToString(Reader["TARGET_ID"])
                };

                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (!UserRight.EmpDataRight.PersonBlackRight.Contains(pr))
                        UserRight.EmpDataRight.PersonBlackRight.Add(pr);
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (!UserRight.MobileEmpRight.PersonBlackRight.Contains(pr))
                        UserRight.MobileEmpRight.PersonBlackRight.Add(pr);
                }
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            return UserRight;
        }

        /// <summary>取得台北公司權限</summary>
        public static Right GetHQRight(AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            //【01】CCP_HQ可看到A1(CCP_HQ已包含A1部門)
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT NAME"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE IS_ACTIVE IN ('A','T')"
                           + " AND substr(DEPT_NO,0,2) IN ('11', '22')"
                           + " AND ABROAD_NAME IS NULL"
                           + " AND PPS_CODE = :PPS_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = UserInfo.PPS_CODE;
            ConnOracle.Open();

            if (!String.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
                UserRight.CanSeeA1 = true;

            ConnOracle.Dispose();

            return UserRight;
        }

        /// <summary>取得(11,22)(A1)管理部權限</summary>
        public static Right GetA1DataRight(AddressBookInfo UserAddressBookInfo, SipUserInfo UserInfo, Right UserRight)
        {
            //【01】檢查User_ID:PPS 的情況
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT USER_ID"
                           + " FROM ADDRESSBOOK_A1_RIGHT"
                           + " WHERE USER_ID = :USER_ID"
                           + " AND USER_TYPE = 'PPS'";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserInfo.PPS_CODE;
            ConnOracle.Open();
            if (!String.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
                UserRight.CanSeeA1 = true;

            //【02】檢查User_ID:DEPT 的情況
            cmd_all = "SELECT USER_ID"
                    + " FROM ADDRESSBOOK_A1_RIGHT"
                    + " WHERE USER_ID = :USER_ID"
                    + " AND USER_ID_2 = :USER_ID_2"
                    + " AND USER_TYPE = 'DEPT'";

            cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":USER_ID", OracleType.VarChar).Value = UserAddressBookInfo.L_Code;
            cmd.Parameters.Add(":USER_ID_2", OracleType.VarChar).Value = UserAddressBookInfo.D_Code;

            if (!String.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
                UserRight.CanSeeA1 = true;

            ConnOracle.Dispose();

            return UserRight;
        }

        /// <summary>檢查EMPDATA權限</summary>
        public static bool CheckRight(RightItems RightItem, string L_Code, string D_Code, string Target_PPS_Code, SipUserInfo UserInfo, Right UserRight)
        {
            //要取得的人員資料：L_Code, D_Code, Target_PPS_Code
            //擁有的權限：UserRight

            DeptRight dr = new DeptRight()
            {
                L_Code = L_Code,
                D_Code = D_Code
            };

            PersonRight pr = new PersonRight()
            {
                PPS_Code = Target_PPS_Code
            };

            bool ReturnRight = false;

            //檢查Admin權限
            if (UserRight.IsAdmin)
            {
                ReturnRight = true;
            }
            else
            {
                if (RightItem == RightItems.EMPDATA_RIGHT)
                {
                    if (UserRight.EmpDataRight.DeptWhiteRight.Contains(dr) || UserRight.EmpDataRight.PersonWhiteRight.Contains(pr))   //檢查白名單權限
                        ReturnRight = true;

                    if (UserRight.EmpDataRight.DeptBlackRight.Contains(dr) || UserRight.EmpDataRight.PersonBlackRight.Contains(pr))  //檢查黑名單權限
                        ReturnRight = false;
                }
                else if (RightItem == RightItems.MOBILE_EMP_RIGHT)
                {
                    if (UserRight.IsAllMobile == true)
                    {
                        ReturnRight = true;
                    }
                    else
                    {
                        if (UserRight.MobileEmpRight.DeptWhiteRight.Contains(dr) || UserRight.MobileEmpRight.PersonWhiteRight.Contains(pr))   //檢查白名單權限
                            ReturnRight = true;

                        if (UserRight.MobileEmpRight.DeptBlackRight.Contains(dr) || UserRight.MobileEmpRight.PersonBlackRight.Contains(pr))  //檢查黑名單權限
                            ReturnRight = false;
                    }
                }
            }

            return ReturnRight;
        }

        /// <summary>取得搜尋第一層資料</summary>
        public static List<PeopleListSearch> GetPeopleListSearch(string keyword, SipUserInfo UserInfo, Right UserRight)
        {
            //【01】取得勘誤表中姓名資料
            keyword = GetErrataPersonName(keyword, Errata.WrongName);

            //【02】取得人員詳細資料
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT PPS_CODE, DEPT_NO"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE IS_ACTIVE IN ('A', 'T')"
                           + " AND NAME LIKE :KEYWORD"
                           + " UNION"
                           + " SELECT B.PPS_CODE, B.DEPT_NO"
                           + " FROM PHONE_BOOK A, PERSON_ALL_MVIEW B"
                           + " WHERE A.PPS_CODE IS NOT NULL"
                           + " AND A.PPS_CODE = B.PPS_CODE"
                           + " AND AREA_CODE||LOCAL_NUMBER LIKE :KEYWORD";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":KEYWORD", OracleType.VarChar).Value = "%" + keyword + "%";

            ConnOracle.Open();
            OracleDataReader Reader = cmd.ExecuteReader();

            List<PeopleDetailInfo> PeopleDetailInfo = new List<PeopleDetailInfo>();
            while (Reader.Read())
            {
                string PPS_Code = Convert.ToString(Reader["PPS_CODE"]);
                string DEPT_NO = Convert.ToString(Reader["DEPT_NO"]);

                AddressBookInfo UserAddressBookInfo = GetAddressBookInfo(DEPT_NO);
                PeopleDetailInfo pd = GetPeopleDetailInfo(UserAddressBookInfo.L_Code, UserAddressBookInfo.D_Code, PPS_Code, UserInfo, UserRight);
                pd.L_Name = GetLocationName(UserAddressBookInfo.L_Code, PPS_Code);
                pd.D_Name = GetDepartmentName(UserAddressBookInfo.D_Code, PPS_Code);
                pd.PPS_Code = PPS_Code;
                //pd.L_Code = null;   //搜尋結果L_Code給null值
                //pd.D_Code = null;   //搜尋結果D_Code給null值
                PeopleDetailInfo.Add(pd);
            }

            Reader.Dispose();
            ConnOracle.Dispose();

            //檢查A1權限
            if (!UserRight.CanSeeA1)
            {
                //遮蔽A1
                List<PeopleDetailInfo> RemoveList = new List<PeopleDetailInfo>();
                foreach (PeopleDetailInfo pdi in PeopleDetailInfo)
                {
                    if (pdi.L_Code == "11,22" && pdi.D_Code == "A1")
                        RemoveList.Add(pdi);
                }
                foreach (PeopleDetailInfo pdi_remove in RemoveList)
                {
                    PeopleDetailInfo.Remove(pdi_remove);
                }

            }

            List<PeopleListSearch> ReturnPeopleListSearch = new List<PeopleListSearch>();
            foreach (PeopleDetailInfo pdi in PeopleDetailInfo)
            {
                PeopleListSearch pls = new PeopleListSearch();
                pls.P_Name = pdi.P_Name;
                pls.PPS_Code = pdi.PPS_Code;
                pls.L_Name = pdi.L_Name;
                pls.D_Name = pdi.D_Name;

                ReturnPeopleListSearch.Add(pls);
            }

            return ReturnPeopleListSearch;
        }

        /// <summary>取得搜尋第二層資料(人員詳細資料)</summary>
        public static List<PeopleDetailInfo> GetPersonDetailInfo(string Target_PPS_Code, SipUserInfo UserInfo, Right UserRight)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT DEPT_NO"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE IS_ACTIVE IN ('A', 'T')"
                           + " AND PPS_CODE = :PPS_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = Target_PPS_Code;

            ConnOracle.Open();
            OracleDataReader Reader = cmd.ExecuteReader();

            List<PeopleDetailInfo> PeopleDetailInfo = new List<PeopleDetailInfo>();
            while (Reader.Read())
            {
                AddressBookInfo UserAddressBookInfo = GetAddressBookInfo(Convert.ToString(Reader["DEPT_NO"]));
                PeopleDetailInfo pd = GetPeopleDetailInfo(UserAddressBookInfo.L_Code, UserAddressBookInfo.D_Code, Target_PPS_Code, UserInfo, UserRight);
                pd.L_Name = GetLocationName(UserAddressBookInfo.L_Code, Target_PPS_Code);
                pd.D_Name = GetDepartmentName(UserAddressBookInfo.D_Code, Target_PPS_Code);
                PeopleDetailInfo.Add(pd);
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            //檢查A1權限
            if (!UserRight.CanSeeA1)
            {
                //遮蔽A1
                List<PeopleDetailInfo> RemoveList = new List<PeopleDetailInfo>();
                foreach (PeopleDetailInfo pdi in PeopleDetailInfo)
                {
                    if (pdi.L_Code == "11,22" && pdi.D_Code == "A1")
                        RemoveList.Add(pdi);
                }
                foreach (PeopleDetailInfo pdi_remove in RemoveList)
                {
                    PeopleDetailInfo.Remove(pdi_remove);
                }

            }

            return PeopleDetailInfo;
        }

        /// <summary>取得人員識別證資料(無權限認證)</summary>
        public static List<PeopleDetailInfo> GetPersonIdInfo(string PPS_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT DEPT_NO"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE IS_ACTIVE IN ('A', 'T')"
                           + " AND PPS_CODE = :PPS_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;

            ConnOracle.Open();
            OracleDataReader Reader = cmd.ExecuteReader();

            List<PeopleDetailInfo> PeopleDetailInfo = new List<PeopleDetailInfo>();
            while (Reader.Read())
            {
                AddressBookInfo UserAddressBookInfo = GetAddressBookInfo(Convert.ToString(Reader["DEPT_NO"]));
                PeopleDetailInfo pd = GetPeopleDetailInfo(UserAddressBookInfo.L_Code, UserAddressBookInfo.D_Code, PPS_Code);
                pd.L_Name = GetLocationName(UserAddressBookInfo.L_Code, PPS_Code);
                pd.D_Name = GetDepartmentName(UserAddressBookInfo.D_Code, PPS_Code);
                pd.PPS_Code = PPS_Code;
                PeopleDetailInfo.Add(pd);
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            return PeopleDetailInfo;
        }

        /// <summary>取得人員的地點中文名稱(有檢查外派地點)</summary>
        public static string GetLocationName(string L_Code, string Target_PPS_Code)
        {
            //【01】檢查是否外派，若是外派人員則L_Name要改為外派地點
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT ABROAD_NAME"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE IS_ACTIVE IN('A','T')"
                           + " AND PPS_CODE = :PPS_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            ConnOracle.Open();
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = Target_PPS_Code;
            string AbroadName = Convert.ToString(cmd.ExecuteScalar());

            if (!string.IsNullOrEmpty(AbroadName))
            {
                //取得外派地點的Location名稱
                cmd_all = "SELECT LOCATION_NAME"
                        + " FROM COMPANY_FIRM_MAPPING"
                        + " WHERE ABROAD_UNIT = :ABROAD_UNIT";

                cmd = new OracleCommand(cmd_all, ConnOracle);
                cmd.Parameters.Add(":ABROAD_UNIT", OracleType.VarChar).Value = AbroadName;
                string ReturnLocationName = Convert.ToString(cmd.ExecuteScalar());
                ConnOracle.Dispose();

                return ReturnLocationName;
            }
            else
            {
                //取得無外派人員Location名稱
                cmd_all = "SELECT DISTINCT LOCATION_NAME"
                               + " FROM COMPANY_FIRM_MAPPING"
                               + " WHERE L_CODE = :L_CODE";
                cmd = new OracleCommand(cmd_all, ConnOracle);
                cmd.Parameters.Add(":L_CODE", OracleType.VarChar).Value = L_Code;

                string ReturnLocationName = Convert.ToString(cmd.ExecuteScalar());
                ConnOracle.Dispose();

                return ReturnLocationName;
            }
        }

        /// <summary>取得人員的部門中文名稱(有檢查外派地點)</summary>
        public static string GetDepartmentName(string D_Code, string Target_PPS_Code)
        {
            //【01】檢查是否外派，若是外派人員則D_Name要改為外派部門
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT ABROAD_NAME"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE IS_ACTIVE IN('A','T')"
                           + " AND PPS_CODE = :PPS_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            ConnOracle.Open();
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = Target_PPS_Code;
            string AbroadName = Convert.ToString(cmd.ExecuteScalar());
            ConnOracle.Dispose();

            if (!string.IsNullOrEmpty(AbroadName))
                return GetDepartmentName("ABROAD");
            else
                return GetDepartmentName(D_Code);
        }

        /// <summary>取得中文部門名稱(從DEPARTMENT_MAPPING表格取得)</summary>
        public static string GetDepartmentName(string D_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrSipContact());
            string cmd_all = "SELECT D_NAME"
                           + " FROM DEPARTMENT_MAPPING"
                           + " WHERE D_CODE = :D_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":D_CODE", OracleType.VarChar).Value = D_Code;

            ConnOracle.Open();
            string D_Name = Convert.ToString(cmd.ExecuteScalar());
            ConnOracle.Dispose();

            if (string.IsNullOrEmpty(D_Name))
                return D_Code;
            else
                return D_Name;
        }

        /// <summary>取得姓名勘誤表的正確姓名或錯誤姓名 </summary>
        public static string GetErrataPersonName(string Name, Errata Operation)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            string cmd_all = "SELECT OLDVALUE, CORRECTVALUE"
                           + " FROM PERSON_ERRATA"
                           + " WHERE DISABLETIME IS NULL"
                           + " AND OLDVALUE = :NAME"
                           + " OR CORRECTVALUE = :NAME";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":NAME", OracleType.VarChar).Value = Name;
            ConnOracle.Open();
            OracleDataReader Reader = cmd.ExecuteReader();
            string ReturnName = null;

            if (Operation == Errata.CorrectName)
            {
                if (Reader.Read())
                {
                    ReturnName = Convert.ToString(Reader["CORRECTVALUE"]);
                }
            }
            else if (Operation == Errata.WrongName)
            {
                if (Reader.Read())
                {
                    ReturnName = Convert.ToString(Reader["OLDVALUE"]);
                }
            }
            Reader.Dispose();
            ConnOracle.Dispose();

            if (string.IsNullOrEmpty(ReturnName))
                return Name;
            else
                return ReturnName;
        }

        public static void InsertCheckIdLog(string Caller_PPS_Code, string Callee_PPS_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            string cmd_all = "INSERT INTO CHECK_ID_LOG(CALLER, CALLEE, TIME)"
                           + " VALUES(:CALLER, :CALLEE, sysdate)";
            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":CALLER", OracleType.VarChar).Value = Caller_PPS_Code;
            Cmd.Parameters.Add(":CALLEE", OracleType.VarChar).Value = Callee_PPS_Code;

            ConnOracle.Open();
            Cmd.ExecuteNonQuery();
            ConnOracle.Dispose();
        }

        //列舉
        public enum Errata
        { CorrectName, WrongName };

        //物件們
        public class TierObject
        {
            public string Tier { get; set; }
            public string PreviousTier { get; set; }
            public string NextTier { get; set; }
            public object List { get; set; }
        }

        public class LocationList
        {
            public string L_Code { get; set; }
            public string L_Name { get; set; }
        }

        public class DepartmentsList
        {
            public string L_Code { get; set; }
            public string D_Code { get; set; }
            public string D_Name { get; set; }
        }

        public class PeopleList
        {
            public string L_Code { get; set; }
            public string D_Code { get; set; }
            public string P_Name { get; set; }
            public string PPS_Code { get; set; }
            //public string Dept_No { get; set; }
            //public string Duty_Name { get; set; }
            //public string Abroad_Name { get; set; }
            //public string Address { get; set; }
            //public string TEL_No { get; set; }
            //public string Mobile_EMP { get; set; }
            //public string EmailID_2 { get; set; }
        }

        public class NonPeopleList
        {
            public string L_Code { get; set; }
            public string D_Code { get; set; }
            public string N_Name { get; set; }
        }

        public class PeopleDetailInfo
        {
            public string L_Code { get; set; }
            public string L_Name { get; set; }  //for search result
            public string D_Code { get; set; }
            public string D_Name { get; set; }  //for search result
            public string PPS_Code { get; set; }//for search result
            public string P_Name { get; set; }          //人員姓名
            public string Abroad_To { get; set; }        //派駐地點
            public string Duty_Name { get; set; }        //職稱
            public string Email { get; set; }           //Email
            public string Mobile { get; set; }          //行動電話
            public string Abroad_Mobile { get; set; }   //國外行動電話
            public string Tel { get; set; }             //市話
            public string Address { get; set; }         //住址
            public string Contact { get; set; }         //緊急聯絡人
            public string Contact_Mobile { get; set; }   //緊急連絡人行動電話
            public string Contact_Tel { get; set; }      //緊急聯絡人市話
            public string Contact_Address { get; set; }  //緊急連絡人地址

            public List<Sip> SIP { get; set; }   //SIP資訊

            public List<DesktopPhoneList> DesktopPhoneList { get; set; }    //分機號碼
        }

        public class NonPeopleDetailInfo
        {
            public string L_Code { get; set; }
            public string D_Code { get; set; }
            public string N_Name { get; set; }          //分機地點名稱
            public List<DesktopPhoneList> DesktopPhoneList { get; set; }
        }

        public class Sip
        {
            public string SIP_Num { get; set; }         //SIP號碼
            public string Device_Type { get; set; }     //SIP裝置
        }

        public class DesktopPhoneList
        {
            public string Area_Code { get; set; }       //分機區碼
            public string Local_Number { get; set; }    //分機號碼
        }

        //權限
        public class AddressBookInfo
        {
            public string L_Code { get; set; }
            public string D_Code { get; set; }
        }

        public enum RightItems : int    //列舉權限資料項目
        {
            EMPDATA_RIGHT = 0,  //全部
            MOBILE_EMP_RIGHT = 1,     //手機

            /*之後可視情況開啟
            TEL_NO_RIGHT = 2,         //家裡電話
            ADDRESS_RIGHT = 3,        //住址
            CONTACT_RIGHT = 4,        //緊急聯絡人
            MOBILE_CONTACT_RIGHT = 5, //緊急聯絡人手機
            TEL_NO_CONTACT_RIGHT = 6, //緊急聯絡人家裡電話
            ADDRESS_CONTACT_RIGHT = 7 //緊急聯絡人住址
            */
        };

        public class Right
        {
            public EmpDataRight EmpDataRight { get; set; }
            public MobileEmpRight MobileEmpRight { get; set; }
            //若之後要加入其他權限資料項目, 則這邊也要加入對應的Right函式(繼承BaseRight)

            public bool CanSeeA1 { get; set; }      //可否看到管理部權限
            public bool IsAdmin { get; set; }       //管理員最大權限
            public bool IsAllMobile { get; set; }   //可看到所有人的手機權限
        }

        public class EmpDataRight : BaseRight { }

        public class MobileEmpRight : BaseRight { }

        public class BaseRight
        {
            public List<DeptRight> DeptWhiteRight { get; set; }         //部門白名單
            public List<DeptRight> DeptBlackRight { get; set; }         //部門黑名單
            public List<PersonRight> PersonWhiteRight { get; set; }     //人員白名單
            public List<PersonRight> PersonBlackRight { get; set; }     //人員黑名單
        }

        public class DeptRight : IEquatable<DeptRight>
        {
            public string L_Code { get; set; }
            public string D_Code { get; set; }

            public bool Equals(DeptRight other)
            {
                if (other == null)
                    return false;

                if (this.L_Code == other.L_Code && this.D_Code == other.D_Code)
                    return true;
                else
                    return false;
            }
            public override bool Equals(object obj)
            {
                if (obj == null)
                    return false;

                DeptRight dr = obj as DeptRight;
                if (dr == null)
                    return false;
                else
                    return Equals(dr);
            }
            public override int GetHashCode()
            {
                return this.GetHashCode();
            }
        }

        public class PersonRight : IEquatable<PersonRight>
        {
            public string PPS_Code { get; set; }

            public bool Equals(PersonRight other)
            {
                if (other == null)
                    return false;

                if (this.PPS_Code == other.PPS_Code)
                    return true;
                else
                    return false;
            }
            public override bool Equals(object obj)
            {
                if (obj == null)
                    return false;

                PersonRight pr = obj as PersonRight;
                if (pr == null)
                    return false;
                else
                    return Equals(pr);
            }
            public override int GetHashCode()
            {
                return this.GetHashCode();
            }
        }

        public class PeopleListSearch
        {
            public string P_Name { get; set; }
            public string PPS_Code { get; set; }
            public string L_Name { get; set; }
            public string D_Name { get; set; }
        }
    }
}